using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;


namespace SIS.DAL
{
    /// <summary>
    /// The StudentProfileTableDB class is responsible for interacting with the database to retrieve and store information
    /// about StudentProfileTable objects.
    /// </summary>
    public class StudentProfileTableDB
    {
        #region "Public Methods"

        /// <summary>
        /// Gets an instance of StudentProfileTable from the underlying datasource.
        /// </summary>
        /// <param name="studentProfileKey">The unique StudentProfileKey of the StudentProfileTable in the database.</param>
        /// <returns>An StudentProfileTable when the StudentProfileKey was found in the database, or null otherwise.</returns>
        public static StudentProfileTable GetItem(int studentProfileKey)
        {
            StudentProfileTable myStudentProfileTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@studentProfileKey", studentProfileKey);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myStudentProfileTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myStudentProfileTable;
            }
        }


        /// <summary>
        /// Gets an instance of StudentProfileTable from the underlying datasource.
        /// </summary>
        /// <param name="UserAccountKey">The unique UserAccountKey of the StudentProfileTable in the database.</param>
        /// <returns>An StudentProfileTable when the UserAccountKey was found in the database, or null otherwise.</returns>
        public static StudentProfileTable GetItemByUserAccountKey(int UserAccountKey)
        {
            StudentProfileTable myStudentProfileTable = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectSingleItemByUserAccountKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            myStudentProfileTable = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
                return myStudentProfileTable;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetList(int SchoolKey)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectList", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@schoolKey", SchoolKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>
        /// Returns a list with TeacherProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the TeacherProfileTable objects.</returns>
        public static StudentProfileTableList GetList(int SchoolKey, string Name, string Address, string NRICNumber, string AddressTypeCode, string CountryCode, string CityCode, string DistrictCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListBySearch", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@schoolKey", SchoolKey);
                    myCommand.Parameters.AddWithValue("@NRICNumber", NRICNumber);
                    myCommand.Parameters.AddWithValue("@Name", Name);
                    myCommand.Parameters.AddWithValue("@Address", Address);
                    myCommand.Parameters.AddWithValue("@AddressTypeCode", AddressTypeCode);
                    myCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
                    myCommand.Parameters.AddWithValue("@CityCode", CityCode);
                    myCommand.Parameters.AddWithValue("@DistrictCode", DistrictCode);

                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataSearchRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByBloodGroupCode(short BloodGroupCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByBloodGroupCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@bloodGroupCode", BloodGroupCode);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByMartialStatusCode(short MartialStatusCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByMartialStatusCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@martialStatusCode", MartialStatusCode);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByNationalityCode(short NationalityCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByNationalityCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@nationalityCode", NationalityCode);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByRaceCode(short RaceCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByRaceCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@raceCode", RaceCode);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByReligionCode(short ReligionCode)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByReligionCode", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@religionCode", ReligionCode);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Returns a list with StudentProfileTable objects.
        /// </summary>
        /// <returns>A generics List with the StudentProfileTable objects.</returns>
        public static StudentProfileTableList GetListByUserAccountKey(int UserAccountKey)
        {
            StudentProfileTableList tempList = new StudentProfileTableList();
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectListByUserAccountKey", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@userAccountKey", UserAccountKey);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
                return tempList;
            }
        }

        /// <summary>
        /// Get Photo a StudentProfileTable from the database.
        /// </summary>
        /// <param name="schoolKey">The SchoolKey of the SchoolProfileTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static Byte[] GetPhoto(int StudentProfileKey)
        {
            Byte[] imgPhoto = null;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableSelectPhotoSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@StudentProfileKey", StudentProfileKey);
                    myConnection.Open();
                    myCommand.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            while (myReader.Read())
                            {
                                imgPhoto = (Byte[])myReader["Photo"];
                            }
                        }
                        myReader.Close();
                    }
                    myConnection.Close();
                }
            }

            return imgPhoto;
        }
        /// <summary>
        /// Saves a StudentProfileTable in the database.
        /// </summary>
        /// <param name="myStudentProfileTable">The StudentProfileTable instance to save.</param>
        /// <returns>The new StudentProfileKey if the StudentProfileTable is new in the database or the existing StudentProfileKey when an item was updated.</returns>
        public static int Save(StudentProfileTable myStudentProfileTable)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableInsertUpdateSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    if (myStudentProfileTable.StudentProfileKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@studentProfileKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@studentProfileKey", myStudentProfileTable.StudentProfileKey);
                    }

                    if (myStudentProfileTable.UserAccountKey == -1)
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@userAccountKey", myStudentProfileTable.UserAccountKey);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.FirstName))
                    {
                        myCommand.Parameters.AddWithValue("@firstName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@firstName", myStudentProfileTable.FirstName);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.LastName))
                    {
                        myCommand.Parameters.AddWithValue("@lastName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@lastName", myStudentProfileTable.LastName);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.NickName))
                    {
                        myCommand.Parameters.AddWithValue("@NickName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@NickName", myStudentProfileTable.NickName);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.OtherName))
                    {
                        myCommand.Parameters.AddWithValue("@otherName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@otherName", myStudentProfileTable.OtherName);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.Gender))
                    {
                        myCommand.Parameters.AddWithValue("@gender", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@gender", myStudentProfileTable.Gender);
                    }

                    if (myStudentProfileTable.DateOfBirth == null)
                    {
                        myCommand.Parameters.AddWithValue("@dateOfBirth", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@dateOfBirth", myStudentProfileTable.DateOfBirth);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.PlaceOfBirth))
                    {
                        myCommand.Parameters.AddWithValue("@PlaceOfBirth", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@PlaceOfBirth", myStudentProfileTable.PlaceOfBirth);
                    }

                    if (myStudentProfileTable.LiveWith == -1)
                    {
                        myCommand.Parameters.AddWithValue("@LiveWith", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@LiveWith", myStudentProfileTable.LiveWith);
                    }

                    if (myStudentProfileTable.ParentStatus == -1)
                    {
                        myCommand.Parameters.AddWithValue("@ParentStatus", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@ParentStatus", myStudentProfileTable.ParentStatus);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.LegalCustobyName))
                    {
                        myCommand.Parameters.AddWithValue("@LegalCustobyName", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@LegalCustobyName", myStudentProfileTable.LegalCustobyName);
                    }

                    if (myStudentProfileTable.FirstLanguageSpeak == -1)
                    {
                        myCommand.Parameters.AddWithValue("@FirstLanguageSpeak", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@FirstLanguageSpeak", myStudentProfileTable.FirstLanguageSpeak);
                    }

                    if (myStudentProfileTable.LanguageSpeakAtHome == -1)
                    {
                        myCommand.Parameters.AddWithValue("@LanguageSpeakAtHome", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@LanguageSpeakAtHome", myStudentProfileTable.LanguageSpeakAtHome);
                    }

                    if (myStudentProfileTable.NoOfSibling == -1)
                    {
                        myCommand.Parameters.AddWithValue("@NoOfSibling", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@NoOfSibling", Convert.ToInt16(myStudentProfileTable.NoOfSibling));
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.Hobby))
                    {
                        myCommand.Parameters.AddWithValue("@Hobby", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@Hobby", myStudentProfileTable.Hobby);
                    }

                    if (myStudentProfileTable.BloodGroupCode == -1)
                    {
                        myCommand.Parameters.AddWithValue("@bloodGroupCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@bloodGroupCode", myStudentProfileTable.BloodGroupCode);
                    }

                    if (myStudentProfileTable.MartialStatusCode == -1)
                    {
                        myCommand.Parameters.AddWithValue("@martialStatusCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@martialStatusCode", myStudentProfileTable.MartialStatusCode);
                    }

                    if (myStudentProfileTable.RaceCode == -1)
                    {
                        myCommand.Parameters.AddWithValue("@raceCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@raceCode", myStudentProfileTable.RaceCode);
                    }

                    if (myStudentProfileTable.ReligionCode == -1)
                    {
                        myCommand.Parameters.AddWithValue("@religionCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@religionCode", myStudentProfileTable.ReligionCode);
                    }

                    if (myStudentProfileTable.NationalityCode == -1)
                    {
                        myCommand.Parameters.AddWithValue("@nationalityCode", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@nationalityCode", myStudentProfileTable.NationalityCode);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.NRICNumber))
                    {
                        myCommand.Parameters.AddWithValue("@nRICNumber", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@nRICNumber", myStudentProfileTable.NRICNumber);
                    }

                    if (myStudentProfileTable.DrivingLicenceType == -1)
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenceType", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenceType", myStudentProfileTable.DrivingLicenceType);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.DrivingLicenseNumber))
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenseNumber", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@drivingLicenseNumber", myStudentProfileTable.DrivingLicenseNumber);
                    }

                    if (myStudentProfileTable.TravelDocumentType == -1)
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentType", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentType", myStudentProfileTable.TravelDocumentType);
                    }
                    if (string.IsNullOrEmpty(myStudentProfileTable.TravelDocumentNumber))
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentNumber", DBNull.Value);
                    }

                    else
                    {
                        myCommand.Parameters.AddWithValue("@travelDocumentNumber", myStudentProfileTable.TravelDocumentNumber);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.WebSiteUrl))
                    {
                        myCommand.Parameters.AddWithValue("@webSiteUrl", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@webSiteUrl", myStudentProfileTable.WebSiteUrl);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.DisabilityRemark))
                    {
                        myCommand.Parameters.AddWithValue("@disabilityRemark", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@disabilityRemark", myStudentProfileTable.DisabilityRemark);
                    }

                    if (string.IsNullOrEmpty(myStudentProfileTable.Remark))
                    {
                        myCommand.Parameters.AddWithValue("@remark", DBNull.Value);
                    }
                    else
                    {
                        myCommand.Parameters.AddWithValue("@remark", myStudentProfileTable.Remark);
                    }

                    myCommand.Parameters.AddWithValue("@status", myStudentProfileTable.Status);

                    DbParameter returnValue;
                    returnValue = myCommand.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    myCommand.Parameters.Add(returnValue);

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    result = Convert.ToInt32(returnValue.Value);
                    myConnection.Close();
                }
            }
            return result;
        }


        /// <summary>
        /// Update Photo a StudentProfileTable in the database.
        /// </summary>
        /// <param name="myStudentProfileTable">The StudentProfileTable instance to save.</param>
        /// <returns>The new StudentProfileKey if the StudentProfileTable is new in the database or the existing StudentProfileKey when an item was updated.</returns>
        public static bool UpdatePhoto(int StudentProfileKey, Byte[] _imgPhoto)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableUpdatePhotoSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@studentProfileKey", StudentProfileKey);
                    myCommand.Parameters.AddWithValue("@Photo", _imgPhoto);


                    DbParameter returnValue;
                    returnValue = myCommand.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    myCommand.Parameters.Add(returnValue);

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    result = Convert.ToInt32(returnValue.Value);
                    myConnection.Close();
                }
            }

            return result > 0;
        }

        /// <summary>
        /// Deletes a StudentProfileTable from the database.
        /// </summary>
        /// <param name="studentProfileKey">The StudentProfileKey of the StudentProfileTable to delete.</param>
        /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
        public static bool Delete(int studentProfileKey)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("sprocStudentProfileTableDeleteSingleItem", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@studentProfileKey", studentProfileKey);
                    myConnection.Open();
                    result = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }
            }
            return result > 0;
        }

        #endregion

        /// <summary>
        /// Initializes a new instance of the StudentProfileTable class and fills it with the data fom the IDataRecord.
        /// </summary>
        private static StudentProfileTable FillDataRecord(IDataRecord myDataRecord)
        {
            StudentProfileTable myStudentProfileTable = new StudentProfileTable();
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("StudentProfileKey")))
            {
                myStudentProfileTable.StudentProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("StudentProfileKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
            {
                myStudentProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
            {
                myStudentProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
            {
                myStudentProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NickName")))
            {
                myStudentProfileTable.NickName = myDataRecord.GetString(myDataRecord.GetOrdinal("NickName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
            {
                myStudentProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Photo")))
            {
                myStudentProfileTable.Photo = (byte[])myDataRecord.GetValue(myDataRecord.GetOrdinal("Photo"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
            {
                myStudentProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
            {
                myStudentProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
            {
                myStudentProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LiveWith")))
            {
                myStudentProfileTable.LiveWith = myDataRecord.GetInt16(myDataRecord.GetOrdinal("LiveWith"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LegalCustobyName")))
            {
                myStudentProfileTable.LegalCustobyName = myDataRecord.GetString(myDataRecord.GetOrdinal("LegalCustobyName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
            {
                myStudentProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstLanguageSpeak")))
            {
                myStudentProfileTable.FirstLanguageSpeak = myDataRecord.GetInt16(myDataRecord.GetOrdinal("FirstLanguageSpeak"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LanguageSpeakAtHome")))
            {
                myStudentProfileTable.LanguageSpeakAtHome = myDataRecord.GetInt16(myDataRecord.GetOrdinal("LanguageSpeakAtHome"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NoOfSibling")))
            {
                myStudentProfileTable.NoOfSibling = Convert.ToSByte(myDataRecord.GetByte(myDataRecord.GetOrdinal("NoOfSibling")));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
            {
                myStudentProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myStudentProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myStudentProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
            {
                myStudentProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
            {
                myStudentProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
            {
                myStudentProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
            {
                myStudentProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
            {
                myStudentProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
            {
                myStudentProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
            {
                myStudentProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
            {
                myStudentProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
            {
                myStudentProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
            {
                myStudentProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DisabilityRemark")))
            {
                myStudentProfileTable.DisabilityRemark = myDataRecord.GetString(myDataRecord.GetOrdinal("DisabilityRemark"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Remark")))
            {
                myStudentProfileTable.Remark = myDataRecord.GetString(myDataRecord.GetOrdinal("Remark"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
            {
                myStudentProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
            {
                myStudentProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
            {
                myStudentProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
            }
            return myStudentProfileTable;
        }

        private static StudentProfileTable FillDataSearchRecord(IDataRecord myDataRecord)
        {
            StudentProfileTable myStudentProfileTable = new StudentProfileTable();
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("StudentProfileKey")))
            {
                myStudentProfileTable.StudentProfileKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("StudentProfileKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
            {
                myStudentProfileTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstName")))
            {
                myStudentProfileTable.FirstName = myDataRecord.GetString(myDataRecord.GetOrdinal("FirstName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LastName")))
            {
                myStudentProfileTable.LastName = myDataRecord.GetString(myDataRecord.GetOrdinal("LastName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NickName")))
            {
                myStudentProfileTable.NickName = myDataRecord.GetString(myDataRecord.GetOrdinal("NickName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("OtherName")))
            {
                myStudentProfileTable.OtherName = myDataRecord.GetString(myDataRecord.GetOrdinal("OtherName"));
            }
            
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Gender")))
            {
                myStudentProfileTable.Gender = myDataRecord.GetString(myDataRecord.GetOrdinal("Gender"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DateOfBirth")))
            {
                myStudentProfileTable.DateOfBirth = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("DateOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PlaceOfBirth")))
            {
                myStudentProfileTable.PlaceOfBirth = myDataRecord.GetString(myDataRecord.GetOrdinal("PlaceOfBirth"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LiveWith")))
            {
                myStudentProfileTable.LiveWith = myDataRecord.GetInt16(myDataRecord.GetOrdinal("LiveWith"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LegalCustobyName")))
            {
                myStudentProfileTable.LegalCustobyName = myDataRecord.GetString(myDataRecord.GetOrdinal("LegalCustobyName"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ParentStatus")))
            {
                myStudentProfileTable.ParentStatus = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ParentStatus"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FirstLanguageSpeak")))
            {
                myStudentProfileTable.FirstLanguageSpeak = myDataRecord.GetInt16(myDataRecord.GetOrdinal("FirstLanguageSpeak"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LanguageSpeakAtHome")))
            {
                myStudentProfileTable.LanguageSpeakAtHome = myDataRecord.GetInt16(myDataRecord.GetOrdinal("LanguageSpeakAtHome"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NoOfSibling")))
            {
                myStudentProfileTable.NoOfSibling = Convert.ToSByte(myDataRecord.GetByte(myDataRecord.GetOrdinal("NoOfSibling")));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Hobby")))
            {
                myStudentProfileTable.Hobby = myDataRecord.GetString(myDataRecord.GetOrdinal("Hobby"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myStudentProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BloodGroupCode")))
            {
                myStudentProfileTable.BloodGroupCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("BloodGroupCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("MartialStatusCode")))
            {
                myStudentProfileTable.MartialStatusCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("MartialStatusCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RaceCode")))
            {
                myStudentProfileTable.RaceCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("RaceCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ReligionCode")))
            {
                myStudentProfileTable.ReligionCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("ReligionCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NationalityCode")))
            {
                myStudentProfileTable.NationalityCode = myDataRecord.GetInt16(myDataRecord.GetOrdinal("NationalityCode"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("NRICNumber")))
            {
                myStudentProfileTable.NRICNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("NRICNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenceType")))
            {
                myStudentProfileTable.DrivingLicenceType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("DrivingLicenceType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DrivingLicenseNumber")))
            {
                myStudentProfileTable.DrivingLicenseNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("DrivingLicenseNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentType")))
            {
                myStudentProfileTable.TravelDocumentType = myDataRecord.GetInt16(myDataRecord.GetOrdinal("TravelDocumentType"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TravelDocumentNumber")))
            {
                myStudentProfileTable.TravelDocumentNumber = myDataRecord.GetString(myDataRecord.GetOrdinal("TravelDocumentNumber"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("WebSiteUrl")))
            {
                myStudentProfileTable.WebSiteUrl = myDataRecord.GetString(myDataRecord.GetOrdinal("WebSiteUrl"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("DisabilityRemark")))
            {
                myStudentProfileTable.DisabilityRemark = myDataRecord.GetString(myDataRecord.GetOrdinal("DisabilityRemark"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Remark")))
            {
                myStudentProfileTable.Remark = myDataRecord.GetString(myDataRecord.GetOrdinal("Remark"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
            {
                myStudentProfileTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
            {
                myStudentProfileTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
            }
            if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
            {
                myStudentProfileTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
            }
            return myStudentProfileTable;
        }
    }

}
